package com.ztesoft.sca.dao;

import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import com.ztesoft.sca.model.ImAreaDto;
import com.ztesoft.sca.model.user.ImOrganizationPo;

@Component
public class ImAreaDaoImpl implements ImAreaDao{
    private JdbcTemplate jdbcTemplate;

    @Autowired
    public ImAreaDaoImpl(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
    
    @Override
    public void insertImArea(ImAreaDto imArea) throws Exception {
        StringBuffer sqlBuf = new StringBuffer();
        sqlBuf.append(" insert into IM_AREA ")                
              .append(" (area_id, parent_id, area_code, area_name, code, c3_code) " )
              .append(" VALUES (?,?,?,?,?,?) ");
        Object[] param = new Object[]{imArea.getAreaId(),imArea.getParentId(),imArea.getAreaCode(),imArea.getAreaName(),imArea.getCode(),imArea.getC3Code()};
        this.jdbcTemplate.update(sqlBuf.toString(),param);
    }

	@Override
	public void delImArea(ImAreaDto imArea) throws Exception {
        String sqlStr = " delete from IM_AREA where area_id=? ";
        Object[] param = new Object[]{imArea.getAreaId()};
        this.jdbcTemplate.update(sqlStr,param);
    }

	@Override
	public void modifyImArea(ImAreaDto imArea) throws Exception {
		StringBuffer sqlBuf = new StringBuffer();
        sqlBuf.append(" update IM_AREA set area_code=?, area_name=?, code=?, c3_code=? WHERE area_id=? ");   
        Object[] param = new Object[]{imArea.getAreaCode(),imArea.getAreaName(),imArea.getCode(),imArea.getC3Code(),imArea.getAreaId()};
        this.jdbcTemplate.update(sqlBuf.toString(),param);
    }

	@Override
	public List<ImAreaDto> listImArea(ImAreaDto imArea, int from, int size) throws Exception {
    	StringBuffer sqlBuf = new StringBuffer();
    	List qryList=new ArrayList();
    	sqlBuf.append(" SELECT * FROM (SELECT ROWNUM rn,area_id, parent_id, area_code, area_name, code, c3_code ")
        	  .append(" FROM (SELECT area_id, parent_id, area_code, area_name, code, c3_code FROM IM_AREA WHERE 1=1 ");
    	  
    	  if(imArea.getAreaId()!=null){
	  		  sqlBuf.append(" AND area_id=? ");   
	  		  qryList.add(imArea.getAreaId());
	  	  }
    	  if(imArea.getParentId()!=null){
	  		  sqlBuf.append(" AND parent_id=? ");   
	  		  qryList.add(imArea.getParentId());
	  	  }
    	  
	  	  if(imArea.getAreaCode()!=null && !"".equals(imArea.getAreaCode())){
	  		  sqlBuf.append(" AND area_code=? ");   
	  		  qryList.add(imArea.getAreaCode());
	  	  }
	  	  if(imArea.getAreaName()!=null && !"".equals(imArea.getAreaName())){
	  		  sqlBuf.append(" AND area_name=? ");   
	  		  qryList.add(imArea.getAreaName());
	  	  }
	  	  if(imArea.getCode()!=null && !"".equals(imArea.getCode())){
	  		  sqlBuf.append(" AND code=? ");   
	  		  qryList.add(imArea.getCode());
	  	  }
	  	  if(imArea.getC3Code()!=null && !"".equals(imArea.getC3Code())){
	  		  sqlBuf.append(" AND c3_code=? ");   
	  		  qryList.add(imArea.getC3Code());
	  	  }
	  	      	
	  	sqlBuf.append(" ) WHERE ROWNUM<=? ORDER BY area_id asc) t2 WHERE T2.rn >=? ");
    	
        if (from < 0) {
            from = Integer.MAX_VALUE; 
        }
        
        qryList.add(size);
        qryList.add(from);
        Object[] param =qryList.toArray(); 
        List<ImAreaDto> list = this.jdbcTemplate.query(sqlBuf.toString(),param,BeanPropertyRowMapper.newInstance(ImAreaDto.class));
        return list;
    }
	
	
	
	@Override
	public List<ImOrganizationPo> listImAreaOrg(ImAreaDto imArea, int from, int size) throws Exception {
    	StringBuffer sqlBuf = new StringBuffer();
    	List qryList=new ArrayList();
    	sqlBuf.append(" SELECT * FROM (SELECT ROWNUM rn, org_id, area_id, org_code, org_name, parent_id, org_code_path, org_name_path, org_alias ")
        	  .append(" FROM (SELECT B.org_id, B.area_id, B.org_code, B.org_name, B.parent_id, B.org_code_path, B.org_name_path, B.org_alias ")
        	  .append(" FROM IM_AREA A JOIN IM_ORGANIZATION B ON A.AREA_ID=B.AREA_ID WHERE 1=1  ");
    	  
    	  if(imArea.getAreaId()!=null){
	  		  sqlBuf.append(" AND A.area_id=? ");   
	  		  qryList.add(imArea.getAreaId());
	  	  }
    	  if(imArea.getParentId()!=null){
	  		  sqlBuf.append(" AND A.parent_id=? ");   
	  		  qryList.add(imArea.getParentId());
	  	  }
    	  
	  	  if(imArea.getAreaCode()!=null && !"".equals(imArea.getAreaCode())){
	  		  sqlBuf.append(" AND A.area_code=? ");   
	  		  qryList.add(imArea.getAreaCode());
	  	  }
	  	  if(imArea.getAreaName()!=null && !"".equals(imArea.getAreaName())){
	  		  sqlBuf.append(" AND A.area_name=? ");   
	  		  qryList.add(imArea.getAreaName());
	  	  }
	  	  if(imArea.getCode()!=null && !"".equals(imArea.getCode())){
	  		  sqlBuf.append(" AND A.code=? ");   
	  		  qryList.add(imArea.getCode());
	  	  }
	  	  if(imArea.getC3Code()!=null && !"".equals(imArea.getC3Code())){
	  		  sqlBuf.append(" AND A.c3_code=? ");   
	  		  qryList.add(imArea.getC3Code());
	  	  }
	  	      	
	  	sqlBuf.append(" ) WHERE ROWNUM<=? ORDER BY B.org_id asc) t2 WHERE T2.rn >=? ");
    	
        if (from < 0) {
            from = Integer.MAX_VALUE; 
        }
        
        qryList.add(size);
        qryList.add(from);
        Object[] param =qryList.toArray(); 
        List<ImOrganizationPo> list = this.jdbcTemplate.query(sqlBuf.toString(),param,BeanPropertyRowMapper.newInstance(ImOrganizationPo.class));
        return list;
    }

	@Override
	public List<ImAreaDto> listImAreaTree(ImAreaDto imArea) throws Exception {
		String sqlStr = "select area_id, parent_id, area_code, area_name, code, c3_code from IM_AREA where 1=1";
        List<Object> qryList = new ArrayList<>();
        if (imArea.getParentId() != null) {
            sqlStr += " start with parent_id = ? connect by prior area_id=parent_id";
            qryList.add(imArea.getParentId());
        } else {
            sqlStr += " start with parent_id is null connect by prior area_id=parent_id";
        }
        return this.jdbcTemplate.query(sqlStr, qryList.toArray(), BeanPropertyRowMapper.newInstance(ImAreaDto.class));		
    }

	
}
